﻿use employees
go
------------------9----------------------------------------------------------------
SELECT 
[Empl].[ID], 
[DEP] .[LastName], 
[DEP].[FirstName]
FROM [dbo].[Empl] INNER JOIN (SELECT 
[Empl].[FirstName],
[Empl].[LastName] 
FROM [dbo].[Empl] 
WHERE [Empl].[DeptId] IN (SELECT 
[Dept].[ID] 
FROM [dbo].[Dept] 
WHERE [Dept].[City] LIKE N'Lvov')  
GROUP BY [Empl].[FirstName], [Empl].[LastName]  
HAVING Count([Empl].[LastName])>=2) [DEP] 
ON [Empl].FirstName=[DEP].[FirstName] AND [Empl].[LastName]=[DEP].[LastName]
ORDER BY [DEP] .[FirstName]

SELECT DISTINCT 
[Empl].[ID], 
[Empl].[FirstName], 
[Empl].[LastName] 
FROM [dbo].[Empl],[dbo].[Empl] [E2]
WHERE [Empl].[ID]<>[E2].[ID] AND [Empl].[FirstName]=[E2].[FirstName] AND [Empl].[LastName]=[E2].[LastName] 
AND [Empl].[DeptID]=[E2].[DeptID] AND [Empl].[DeptID] IN (SELECT 
[Dept].[ID] 
FROM [dbo].[Dept] 
WHERE [Dept].[City] LIKE N'Lvov')
ORDER BY [Empl].[FirstName]
